------明细-------------------------------------------------------------------------------------------------
---刷t-1；
with t_network as(
    select
        id,
        code as network_code,
        name as network_name,
        agent_id as subordinate_agent_id,
        agent_code as subordinate_agent_code,
        agent_name as subordinate_agent_name,
        network_type
    from jms_dim.dim_network_whole_massage
),
     t_first_code as (
         select
             first_code as code,
             center_name as end_center_name,
             center_code as end_center_code
         from jms_dim.dim_first_second_third_code_info
         where code_type=1
     ),
     t_arrival_first as(
         select
             billcode
              , scansitecode
              , inputsite
              , scantime
              , packagecode
              , nextstationcode
              , nextstation
         from(
                 select waybill_no as billcode
                      , scan_site_code as scansitecode
                      , scan_site as inputsite
                      , scan_time as scantime
                      , package_code as packagecode
                      , pre_station_code as nextstationcode
                      , pre_station as nextstation
                      , row_number() over (partition by scan_site_code,waybill_no order by scan_time desc) bill_rank
                 from jms_dwd.dwd_tab_barscan_centerarrival_base_dt
                 WHERE dt = '{{ execution_date |  cst_ds }}' and network_type=2
             ) tmp where bill_rank = 1
     ),
     t_arrival_seven as(
         select
             billcode
              , scansitecode
              , inputsite
              , scantime
              , packagecode
              , nextstationcode
              , nextstation
         from(
                 select waybill_no as billcode
                      , scan_site_code as scansitecode
                      , scan_site as inputsite
                      , scan_time as scantime
                      , package_code as packagecode
                      , pre_station_code as nextstationcode
                      , pre_station as nextstation
                      , row_number() over (partition by scan_site_code,waybill_no order by scan_time desc) bill_rank
                 from jms_dwd.dwd_tab_barscan_centerarrival_base_dt
                 WHERE dt between date_sub('{{ execution_date |  cst_ds }}',7) and date_sub('{{ execution_date |  cst_ds }}',0) and network_type=2
             ) tmp where bill_rank = 1
     ),
     t_arrival_result as(
         select t_arrival_first.*
         from t_arrival_first
                  inner join t_arrival_seven
             ---中心不一样但是单号一样,当天则是要比较扫描时间
                             on t_arrival_first.scansitecode != t_arrival_seven.scansitecode and t_arrival_first.billcode = t_arrival_seven.billcode
    and t_arrival_first.scantime > t_arrival_seven.scantime
    ),
     ---发件表关联
    t_send as(
select
    billcode
        , scansitecode
        , inputsite
        , scantime
        , packagecode
        , nextstationcode
        , nextstation
from(
    select waybill_no as billcode
        , scan_site_code as scansitecode
        , scan_site as inputsite
        , scan_time as scantime
        , package_code as packagecode
        , next_station_code as nextstationcode
        , next_station as nextstation
        , row_number() over (partition by scan_site_code,waybill_no order by scan_time asc) bill_rank --中心最晚发件
    from jms_dwd.dwd_tab_barscan_centersend_base_dt
    ---这里要当天的就行
    WHERE dt between date_sub('{{ execution_date |  cst_ds }}',0) and '{{ execution_date |  cst_ds }}'
    and next_network_type =2
    and nvl(cast(mobile AS decimal(13, 3)), 0) <= 50
    and nvl(cast(remark AS decimal(13, 3)), 0) <= 50
    and nvl(cast(remark1 AS decimal(13, 3)), 0) <= 50
    and nvl(cast(remark1 AS decimal(13, 3)), 0)+nvl(cast(remark AS decimal(13, 3)), 0)+nvl(cast(mobile AS decimal(13, 3)), 0)<=90
    ) t where bill_rank = 1
    ),
        ---发件表关联，得到应建包的包号
    t_send_sub as(
select
    t_arrival_result.billcode
        ,t_arrival_result.scansitecode
        ,t_arrival_result.inputsite
        ,t_arrival_result.scantime
        ,t_send.packagecode
        ,t_send.nextstationcode
        ,t_send.nextstation
from t_arrival_result
    inner join t_send on t_arrival_result.scansitecode = t_send.scansitecode and t_arrival_result.billcode = t_send.billcode
    ),
    ---获得中心代理去区
    t_send_center as(
select t_send_sub.*
        , subordinate_agent_code as center_agent_code
        , subordinate_agent_name as center_agent_name
from t_send_sub
    left join t_network on t_send_sub.scansitecode = t_network.network_code

    ),
    ---只要网点往中心,t_send_center
    ---运单表去重
    t_waybill as (

select * from (
    select
    waybill_no
        , GOODS_TYPE_CODE
        , pick_network_code
        , pick_network_name
        , package_charge_volume
        , PACKAGE_INSIDE_CHARGE_WEIGHT
        , first_code
        , input_time
        , package_length
        , package_wide
        , package_high
        , customer_code
        , customer_name
    from(
    select
    waybill_no
        , GOODS_TYPE_CODE
        , pick_network_code
        , pick_network_name
        , concat(nvl(package_length,0.0), '*', nvl(package_wide,0.0), '*', nvl(package_high,0.0)) as package_charge_volume
        , nvl(PACKAGE_INSIDE_CHARGE_WEIGHT,0) as PACKAGE_INSIDE_CHARGE_WEIGHT
        , split(regexp_replace(terminal_dispatch_code, ' ', ','), ',')[0] as first_code
        , input_time
        , last_update_time
        , package_length
        , package_wide
        , package_high
        , customer_code
        , customer_name
    from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
    WHERE dt BETWEEN date_format(date_sub('{{ execution_date |  cst_ds }}',7),'yyyy-MM-dd') AND '{{ execution_date |  cst_ds }}'
    ) tmp1 ) tmp2
where  package_length<=50 and package_wide<=50 and package_high<=50 and PACKAGE_INSIDE_CHARGE_WEIGHT <=3
  and nvl(package_length,0) + nvl(package_wide,0) + nvl(package_high,0)<=90

    ),
---innerjoin获得小件28360802
    t_union_send_waybill as (
select
    t_send_center.billcode
        , t_send_center.scansitecode
        , t_send_center.inputsite
        , t_send_center.scantime
        , t_send_center.packagecode
        , t_send_center.nextstationcode
        , t_send_center.nextstation
        , t_send_center.center_agent_code
        , t_send_center.center_agent_name
        , t_waybill.GOODS_TYPE_CODE
        , t_waybill.pick_network_code
        , t_waybill.pick_network_name
        , t_waybill.package_charge_volume
        , t_waybill.PACKAGE_INSIDE_CHARGE_WEIGHT
        , t_waybill.first_code
        , t_waybill.customer_code
        , t_waybill.customer_name
from t_send_center inner join t_waybill
on t_send_center.billcode = t_waybill.waybill_no
    ),
    ---剔除在本中心做到件时已有所属包号且包号等于发件所属包号的单号
    ---统计包号相等多少个
    ---总数：18834329
    ---包号相等：15654640
    ---包号不相等：3179689
    t_arrival as(
select
    billcode
        , scansitecode
        , packagecode
from(
    select waybill_no as billcode
        ,scan_site_code as scansitecode
        ,package_code as  packagecode
        , row_number() over (partition by waybill_no,scan_site_code order by scan_time) bill_rank
    from jms_dwd.dwd_tab_barscan_centerarrival_base_dt
    WHERE dt BETWEEN date_format(date_sub('{{ execution_date |  cst_ds }}',7),'yyyy-MM-dd') AND '{{ execution_date |  cst_ds }}'
    ) t where bill_rank = 1
    ),
    t_union_send_arrival as(
select
    t_union_send_waybill.billcode
        , t_union_send_waybill.scansitecode
        , t_union_send_waybill.inputsite
        , t_union_send_waybill.scantime
        , t_union_send_waybill.packagecode
        , t_union_send_waybill.nextstationcode
        , t_union_send_waybill.nextstation
        , t_union_send_waybill.center_agent_code
        , t_union_send_waybill.center_agent_name
        , t_union_send_waybill.GOODS_TYPE_CODE
        , t_union_send_waybill.pick_network_code
        , t_union_send_waybill.pick_network_name
        , t_union_send_waybill.package_charge_volume
        , t_union_send_waybill.PACKAGE_INSIDE_CHARGE_WEIGHT
        , t_union_send_waybill.first_code
        , t_union_send_waybill.customer_code
        , t_union_send_waybill.customer_name
from t_union_send_waybill
    left join t_arrival
on t_union_send_waybill.billcode=t_arrival.billcode and t_union_send_waybill.scansitecode=t_arrival.scansitecode
where t_union_send_waybill.packagecode != t_arrival.packagecode or t_union_send_waybill.packagecode is null or t_arrival.packagecode is null
    ),
---剔除揽收网点所属代理区不等于本中心所属代理区的单号
---关联获得揽收网点的代理区
---2778292
    t_union_send_arrival_network as(
select
    t_union_send_arrival.billcode
        , t_union_send_arrival.scansitecode
        , t_union_send_arrival.inputsite
        , t_union_send_arrival.center_agent_code
        , t_union_send_arrival.center_agent_name
        , t_union_send_arrival.scantime
        , t_union_send_arrival.packagecode
        , t_union_send_arrival.nextstationcode
        , t_union_send_arrival.nextstation
        , t_union_send_arrival.GOODS_TYPE_CODE
        , t_union_send_arrival.pick_network_code
        , t_union_send_arrival.pick_network_name
        , t_union_send_arrival.package_charge_volume
        , t_union_send_arrival.PACKAGE_INSIDE_CHARGE_WEIGHT
        , t_union_send_arrival.first_code
        , t_union_send_arrival.customer_code
        , t_union_send_arrival.customer_name
from t_union_send_arrival left join t_network
on t_union_send_arrival.pick_network_code = t_network.network_code
    left join
    ( --剔除备案客户的运单
    select
    p1.customer_code
    from jms_dim.dim_problem_express_wo_record_base p1
    inner join jms_dim.dim_pew_record_type_base p2 on p1.record_type_id =p2.id and p2.is_enable=1 and p2.is_delete=1
    where p1.record_effect_status=1  and p1.is_enable=1 and p1.is_delete=1
    and '{{ execution_date |  cst_ds }}' between date(p1.contract_effective_time) and  date(p1.contract_expiration_time)
    and p2.type_name='小件不建包备案'
    group by p1.customer_code
    ) p on t_union_send_arrival.customer_code=p.customer_code
where t_union_send_arrival.center_agent_code = t_network.subordinate_agent_code and p.customer_code is null
    ),
---关联一段码表获得末端中心
---按快件一段码进行分组，剔除单流向小件小于10票的单号
---2767205
    t_union_first as(
select
    billcode
        , scansitecode as center_code  ---改为中心
        , inputsite as center_name
        , center_agent_code
        , center_agent_name
        , scantime
        , packagecode
        , nextstationcode
        , nextstation
        , GOODS_TYPE_CODE
        , pick_network_code
        , pick_network_name
        , package_charge_volume
        , PACKAGE_INSIDE_CHARGE_WEIGHT
        , end_center_code
        , end_center_name
        , customer_code
        , customer_name
from(
    select
    billcode
        , scansitecode
        , inputsite
        , center_agent_code
        , center_agent_name
        , scantime
        , packagecode
        , nextstationcode
        , nextstation
        , GOODS_TYPE_CODE
        , pick_network_code
        , pick_network_name
        , package_charge_volume
        , PACKAGE_INSIDE_CHARGE_WEIGHT
        , end_center_code
        , end_center_name
        , count(1) over (partition by scansitecode,first_code) as cnt
        , customer_code
        , customer_name
    from t_union_send_arrival_network
    left join t_first_code on t_union_send_arrival_network.first_code = t_first_code.code
    ) tmp1 where cnt >=10
    ),
           ---is_bagging
           ---要不要去重呢？
           ---总数:2767205
           ---distinct：2767205,center_code,billcode
    t_bagging as(
select
    center_agent_code
        , center_agent_name
        , center_code
        , center_name
        , billcode
        , PACKAGE_INSIDE_CHARGE_WEIGHT
        , package_charge_volume
        , pick_network_code
        , pick_network_name
        , end_center_code
        , end_center_name
        , is_bagging
        , update_time
        , customer_code
        , customer_name
        , dt
from (
    select
    center_agent_code
        , center_agent_name
        , center_code
        , center_name
        , billcode
        , PACKAGE_INSIDE_CHARGE_WEIGHT
        , package_charge_volume
        , pick_network_code
        , pick_network_name
        , end_center_code
        , end_center_name
        , case when(packagecode is not null) then 1 else 0 end as is_bagging
        , to_date(scantime) as update_time   ---到件时间作为更新时间
        , to_date(scantime)  as dt
        , row_number() over(partition by center_code,billcode order by scantime desc) as rk
        , customer_code
        , customer_name
    from t_union_first
    ) tmp where rk = 1 and update_time = '{{ execution_date |  cst_ds }}'
    )
insert overwrite table bidefault.dm_transfor_network_package_detail partition(dt)
select * from t_bagging distribute by dt,abs(hash(billcode))%4 ;

---------------------------------------------------------------------------
--刷t-7
with t_network as(
    select
        id,
        code as network_code,
        name as network_name,
        agent_id as subordinate_agent_id,
        agent_code as subordinate_agent_code,
        agent_name as subordinate_agent_name,
        network_type
    from jms_dim.dim_network_whole_massage
),
     t_first_code as (
         select
             first_code as code,
             center_name as end_center_name,
             center_code as end_center_code
         from jms_dim.dim_first_second_third_code_info
         where code_type=1
     ),
     t_arrival_first as(
         select
             billcode
              , scansitecode
              , inputsite
              , scantime
              , packagecode
              , nextstationcode
              , nextstation
         from(
                 select waybill_no as billcode
                      , scan_site_code as scansitecode
                      , scan_site as inputsite
                      , scan_time as scantime
                      , package_code as packagecode
                      , pre_station_code as nextstationcode
                      , pre_station as nextstation
                      , row_number() over (partition by scan_site_code,waybill_no order by scan_time desc) bill_rank
                 from jms_dwd.dwd_tab_barscan_centerarrival_base_dt
                 WHERE dt = date_add('{{ execution_date |  cst_ds }}',-6) and network_type=2
             ) tmp where bill_rank = 1
     ),
     t_arrival_seven as(
         select
             billcode
              , scansitecode
              , inputsite
              , scantime
              , packagecode
              , nextstationcode
              , nextstation
         from(
                 select waybill_no as billcode
                      , scan_site_code as scansitecode
                      , scan_site as inputsite
                      , scan_time as scantime
                      , package_code as packagecode
                      , pre_station_code as nextstationcode
                      , pre_station as nextstation
                      , row_number() over (partition by scan_site_code,waybill_no order by scan_time desc) bill_rank
                 from jms_dwd.dwd_tab_barscan_centerarrival_base_dt
                 WHERE dt between date_sub(date_add('{{ execution_date |  cst_ds }}',-6),7) and date_sub(date_add('{{ execution_date |  cst_ds }}',-6),0) and network_type=2
             ) tmp where bill_rank = 1
     ),
     t_arrival_result as(
         select t_arrival_first.*
         from t_arrival_first
                  inner join t_arrival_seven
             ---中心不一样但是单号一样,当天则是要比较扫描时间
                             on t_arrival_first.scansitecode != t_arrival_seven.scansitecode and t_arrival_first.billcode = t_arrival_seven.billcode
    and t_arrival_first.scantime > t_arrival_seven.scantime
    ),
     ---发件表关联
    t_send as(
select
    billcode
        , scansitecode
        , inputsite
        , scantime
        , packagecode
        , nextstationcode
        , nextstation
from(
    select waybill_no as billcode
        , scan_site_code as scansitecode
        , scan_site as inputsite
        , scan_time as scantime
        , package_code as packagecode
        , next_station_code as nextstationcode
        , next_station as nextstation
        , row_number() over (partition by scan_site_code,waybill_no order by scan_time asc) bill_rank --中心最晚发件
    from jms_dwd.dwd_tab_barscan_centersend_base_dt
    ---这里要当天的就行
    WHERE dt between date_sub(date_add('{{ execution_date |  cst_ds }}',-6),0) and date_add('{{ execution_date |  cst_ds }}',-6)
    and next_network_type =2
    and nvl(cast(mobile AS decimal(13, 3)), 0) <= 50
    and nvl(cast(remark AS decimal(13, 3)), 0) <= 50
    and nvl(cast(remark1 AS decimal(13, 3)), 0) <= 50
    and nvl(cast(mobile AS decimal(13, 3)), 0)+ nvl(cast(remark AS decimal(13, 3)), 0)+nvl(cast(remark1 AS decimal(13, 3)), 0)<=90
    ) t where bill_rank = 1
    ),
        ---发件表关联，得到应建包的包号
    t_send_sub as(
select
    t_arrival_result.billcode
        ,t_arrival_result.scansitecode
        ,t_arrival_result.inputsite
        ,t_arrival_result.scantime
        ,t_send.packagecode
        ,t_send.nextstationcode
        ,t_send.nextstation
from t_arrival_result
    inner join t_send on t_arrival_result.scansitecode = t_send.scansitecode and t_arrival_result.billcode = t_send.billcode
    ),
    ---获得中心代理去区
    t_send_center as(
select t_send_sub.*
        , subordinate_agent_code as center_agent_code
        , subordinate_agent_name as center_agent_name
from t_send_sub
    left join t_network on t_send_sub.scansitecode = t_network.network_code

    ),
    ---只要网点往中心,t_send_center
    ---运单表去重
    t_waybill as (

select * from (
    select
    waybill_no
        , GOODS_TYPE_CODE
        , pick_network_code
        , pick_network_name
        , package_charge_volume
        , PACKAGE_INSIDE_CHARGE_WEIGHT
        , first_code
        , input_time
        , package_length
        , package_wide
        , package_high
        , customer_code
        , customer_name
    from(
    select
    waybill_no
        , GOODS_TYPE_CODE
        , pick_network_code
        , pick_network_name
        , concat(nvl(package_length,0.0), '*', nvl(package_wide,0.0), '*', nvl(package_high,0.0)) as package_charge_volume
        , nvl(PACKAGE_INSIDE_CHARGE_WEIGHT,0) as PACKAGE_INSIDE_CHARGE_WEIGHT
        , split(regexp_replace(terminal_dispatch_code, ' ', ','), ',')[0] as first_code
        , input_time
        , last_update_time
        , package_length
        , package_wide
        , package_high
        , customer_code
        , customer_name
    from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
    WHERE dt BETWEEN date_format(date_sub(date_add('{{ execution_date |  cst_ds }}',-6),7),'yyyy-MM-dd') AND date_add('{{ execution_date |  cst_ds }}',-6)
    ) tmp1 ) tmp2
where  package_length<=50 and package_wide<=50 and package_high<=50 and PACKAGE_INSIDE_CHARGE_WEIGHT <=3
  and package_length+package_wide+package_high<=90

    ),
---innerjoin获得小件28360802
    t_union_send_waybill as (
select
    t_send_center.billcode
        , t_send_center.scansitecode
        , t_send_center.inputsite
        , t_send_center.scantime
        , t_send_center.packagecode
        , t_send_center.nextstationcode
        , t_send_center.nextstation
        , t_send_center.center_agent_code
        , t_send_center.center_agent_name
        , t_waybill.GOODS_TYPE_CODE
        , t_waybill.pick_network_code
        , t_waybill.pick_network_name
        , t_waybill.package_charge_volume
        , t_waybill.PACKAGE_INSIDE_CHARGE_WEIGHT
        , t_waybill.first_code
        , t_waybill.customer_code
        , t_waybill.customer_name
from t_send_center inner join t_waybill
on t_send_center.billcode = t_waybill.waybill_no
    ),
    ---剔除在本中心做到件时已有所属包号且包号等于发件所属包号的单号
    ---统计包号相等多少个
    ---总数：18834329
    ---包号相等：15654640
    ---包号不相等：3179689
    t_arrival as(
select
    billcode
        , scansitecode
        , packagecode
from(
    select waybill_no as billcode
        ,scan_site_code as scansitecode
        ,package_code as  packagecode
        , row_number() over (partition by waybill_no,scan_site_code order by scan_time) bill_rank
    from jms_dwd.dwd_tab_barscan_centerarrival_base_dt
    WHERE dt BETWEEN date_format(date_sub(date_add('{{ execution_date |  cst_ds }}',-6),7),'yyyy-MM-dd') AND date_add('{{ execution_date |  cst_ds }}',-6)
    ) t where bill_rank = 1
    ),
    t_union_send_arrival as(
select
    t_union_send_waybill.billcode
        , t_union_send_waybill.scansitecode
        , t_union_send_waybill.inputsite
        , t_union_send_waybill.scantime
        , t_union_send_waybill.packagecode
        , t_union_send_waybill.nextstationcode
        , t_union_send_waybill.nextstation
        , t_union_send_waybill.center_agent_code
        , t_union_send_waybill.center_agent_name
        , t_union_send_waybill.GOODS_TYPE_CODE
        , t_union_send_waybill.pick_network_code
        , t_union_send_waybill.pick_network_name
        , t_union_send_waybill.package_charge_volume
        , t_union_send_waybill.PACKAGE_INSIDE_CHARGE_WEIGHT
        , t_union_send_waybill.first_code
        , t_union_send_waybill.customer_code
        , t_union_send_waybill.customer_name
from t_union_send_waybill
    left join t_arrival
on t_union_send_waybill.billcode=t_arrival.billcode and t_union_send_waybill.scansitecode=t_arrival.scansitecode
where t_union_send_waybill.packagecode != t_arrival.packagecode or t_union_send_waybill.packagecode is null or t_arrival.packagecode is null
    ),
---剔除揽收网点所属代理区不等于本中心所属代理区的单号
---关联获得揽收网点的代理区
---2778292
    t_union_send_arrival_network as(
select
    t_union_send_arrival.billcode
        , t_union_send_arrival.scansitecode
        , t_union_send_arrival.inputsite
        , t_union_send_arrival.center_agent_code
        , t_union_send_arrival.center_agent_name
        , t_union_send_arrival.scantime
        , t_union_send_arrival.packagecode
        , t_union_send_arrival.nextstationcode
        , t_union_send_arrival.nextstation
        , t_union_send_arrival.GOODS_TYPE_CODE
        , t_union_send_arrival.pick_network_code
        , t_union_send_arrival.pick_network_name
        , t_union_send_arrival.package_charge_volume
        , t_union_send_arrival.PACKAGE_INSIDE_CHARGE_WEIGHT
        , t_union_send_arrival.first_code
        , t_union_send_arrival.customer_code
        , t_union_send_arrival.customer_name
from t_union_send_arrival left join t_network
on t_union_send_arrival.pick_network_code = t_network.network_code
    left join
    ( --剔除备案客户的运单
    select
    p1.customer_code
    from jms_dim.dim_problem_express_wo_record_base p1
    inner join jms_dim.dim_pew_record_type_base p2 on p1.record_type_id =p2.id and p2.is_enable=1 and p2.is_delete=1
    where p1.record_effect_status=1  and p1.is_enable=1 and p1.is_delete=1
    and date_add('{{ execution_date |  cst_ds }}',-6) between date(p1.contract_effective_time) and  date(p1.contract_expiration_time)
    and p2.type_name='小件不建包备案'
    group by p1.customer_code
    ) p on t_union_send_arrival.customer_code=p.customer_code
where t_union_send_arrival.center_agent_code = t_network.subordinate_agent_code and p.customer_code is null
    ),
---关联一段码表获得末端中心
---按快件一段码进行分组，剔除单流向小件小于10票的单号
---2767205
    t_union_first as(
select
    billcode
        , scansitecode as center_code  ---改为中心
        , inputsite as center_name
        , center_agent_code
        , center_agent_name
        , scantime
        , packagecode
        , nextstationcode
        , nextstation
        , GOODS_TYPE_CODE
        , pick_network_code
        , pick_network_name
        , package_charge_volume
        , PACKAGE_INSIDE_CHARGE_WEIGHT
        , end_center_code
        , end_center_name
        , customer_code
        , customer_name
from(
    select
    billcode
        , scansitecode
        , inputsite
        , center_agent_code
        , center_agent_name
        , scantime
        , packagecode
        , nextstationcode
        , nextstation
        , GOODS_TYPE_CODE
        , pick_network_code
        , pick_network_name
        , package_charge_volume
        , PACKAGE_INSIDE_CHARGE_WEIGHT
        , end_center_code
        , end_center_name
        , count(1) over (partition by scansitecode,first_code) as cnt
        , customer_code
        , customer_name
    from t_union_send_arrival_network
    left join t_first_code on t_union_send_arrival_network.first_code = t_first_code.code
    ) tmp1 where cnt >=10
    ),
           ---is_bagging
           ---要不要去重呢？
           ---总数:2767205
           ---distinct：2767205,center_code,billcode
    t_bagging as(
select
    center_agent_code
        , center_agent_name
        , center_code
        , center_name
        , billcode
        , PACKAGE_INSIDE_CHARGE_WEIGHT
        , package_charge_volume
        , pick_network_code
        , pick_network_name
        , end_center_code
        , end_center_name
        , is_bagging
        , update_time
        , customer_code
        , customer_name
        , dt
from (
    select
    center_agent_code
        , center_agent_name
        , center_code
        , center_name
        , billcode
        , PACKAGE_INSIDE_CHARGE_WEIGHT
        , package_charge_volume
        , pick_network_code
        , pick_network_name
        , end_center_code
        , end_center_name
        , case when(packagecode is not null) then 1 else 0 end as is_bagging
        , to_date(scantime) as update_time   ---到件时间作为更新时间
        , to_date(scantime)  as dt
        , row_number() over(partition by center_code,billcode order by scantime desc) as rk
        , customer_code
        , customer_name
    from t_union_first
    ) tmp where rk = 1 and update_time = date_add('{{ execution_date |  cst_ds }}',-6)
    ) 
insert overwrite table bidefault.dm_transfor_network_package_detail partition(dt)
select * from t_bagging
distribute by dt,abs(hash(billcode))%4;

---汇总-----------------------------------------
--中心维度
insert overwrite table bidefault.dm_transfor_network_center_package_rate partition (dt)
    select
    center_subordinate_agent_code
        , center_subordinate_agent_name
        , center_code
        , center_name
        , sum(is_bagging) as center_real_bagging_total
        , count(1) as center_bagging_total
        , concat(cast(nvl(sum(is_bagging)/count(1),0)*100 as DECIMAL(13,2)),'%') as center_bagging_rate
        , count(1)-sum(is_bagging) as center_unreal_bagging_total
        , update_time as update_time ---问题
        , update_time as dt
    from bidefault.dm_transfor_network_package_detail
    where dt in ( '{{ execution_date |  cst_ds }}',date_add('{{ execution_date |  cst_ds }}',-6))
    group by center_subordinate_agent_code,center_subordinate_agent_name,center_code,center_name,update_time
distribute by dt;

---代理区维度汇总
insert overwrite table bidefault.dm_transfor_network_agent_package_rate partition (dt)
    select
        center_subordinate_agent_code
         , center_subordinate_agent_name
         , sum(is_bagging) as agent_real_bagging_total
         , count(1) as agent_bagging_total
         , concat(cast(nvl(sum(is_bagging)/count(1),0)*100 as DECIMAL(13,2)),'%') as agent_bagging_rate
         , count(1)-sum(is_bagging) as agent_unreal_bagging_total
         , update_time as update_time
         , update_time as dt
    from bidefault.dm_transfor_network_package_detail
    where dt in ( '{{ execution_date |  cst_ds }}',date_add('{{ execution_date |  cst_ds }}',-6))
    group by center_subordinate_agent_code,center_subordinate_agent_name,update_time
distribute by dt;